5  Update Data

5.1 Reference

Default database in this chapter is “ap”.

Other databases included in the simulation MySQL server:

• om
• ex

5.2 Table Operations

Create table

CREATE TABLE invoices_copy AS 
-- everything below can be just a standalong query, you can choose to create the new table with only certain data from the original 
SELECT *
FROM invoices

Locate the newly created table: invoices_copy in ap database:

SHOW TABLES
FROM ap

9 records

Tables_in_ap
general_ledger_accounts
invoice_archive
invoice_archive_copy
invoice_line_items
invoices
invoices_copy
terms
vendor_contacts
vendors

Delete Table

Always use full 3-part naming to be sure deleting the correct table

DROP TABLE ap.invoices_copy

Confirm deleted

SHOW TABLES
FROM ap
WHERE Tables_in_ap = 'invoices_copy'

0 records

Tables_in_ap

Recreate a Table

If you want to remove a table as long as the table exists in the database (Especially useful when you only want to create a temporary table):

DROP TABLE IF EXISTS invoices_copy

5.3 Data Operations

Create backup table for demonstration.

CREATE TABLE invoices_copy AS 
SELECT *
FROM invoices

See the schema for the new table. Easier for the adding new data later…

SELECT *
FROM invoices_copy
LIMIT 1

1 records

invoice_id	vendor_id	invoice_number	invoice_date	invoice_total	payment_total	credit_total	terms_id	invoice_due_date	payment_date
1	122	989319-457	2022-04-08	3813.33	3813.33	0	3	2022-05-08	2022-05-07

Create other backup tables:

DROP TABLE IF EXISTS invoice_archive_copy

CREATE TABLE invoice_archive_copy AS 
SELECT *
FROM invoice_archive

INSERT: Add New Records

Add 1 row to invoices_copy table. Notice that all column values must be entered, even if it is NULL.

INSERT INTO invoices_copy VALUES
(115, 97, '456789', '2022-08-01', 8344.50, 0, 0, 1, '2022-08-31', NULL)

Check the new record is in place:

SELECT *
FROM invoices_copy 
WHERE invoice_id = 115

1 records

invoice_id	vendor_id	invoice_number	invoice_date	invoice_total	payment_total	credit_total	terms_id	invoice_due_date	payment_date
115	97	456789	2022-08-01	8344.5	0	0	1	2022-08-31	NA

Add multiple records in one statement:

INSERT INTO invoices_copy VALUES
    (116, 97, '456701', '2022-08-02', 270.50, 0, 0, 1,
    '2022-09-01', NULL),
    (117, 97, '456791', '2022-08-03', 4390.00, 0, 0, 1,
    '2022-09-02', NULL),
    (118, 97, '456792', '2022-08-03', 565.60, 0, 0, 1,
    '2022-09-02', NULL)

Verify the new records have been added:

SELECT *
FROM ap.invoices_copy 
WHERE invoice_id > 115

3 records

invoice_id	vendor_id	invoice_number	invoice_date	invoice_total	payment_total	credit_total	terms_id	invoice_due_date	payment_date
116	97	456701	2022-08-02	270.5	0	0	1	2022-09-01	NA
117	97	456791	2022-08-03	4390.0	0	0	1	2022-09-02	NA
118	97	456792	2022-08-03	565.6	0	0	1	2022-09-02	NA

You can also copy and add a whole subset of records from one table (source table) to another (target table). To do this, you need to ensure:

• Target table has all the columns in EXACTLY THE SAME names
• Select only the same columns from the source table

Example: To copy a whole chunk of rows to invoice_arhchive.

To start with, the target table invoice_archive_copy has no records…

SELECT * 
FROM invoice_archive_copy

0 records

invoice_id	vendor_id	invoice_number	invoice_date	invoice_total	payment_total	credit_total	terms_id	invoice_due_date	payment_date

Now, we add all invoices (source table) with 0 balance to the archive table.

INSERT INTO invoice_archive_copy
    (invoice_id, vendor_id, invoice_number,
    invoice_total, credit_total, payment_total,
    terms_id, invoice_date, invoice_due_date)
SELECT
    invoice_id, vendor_id, invoice_number,
    invoice_total, credit_total, payment_total,
    terms_id, invoice_date, invoice_due_date
FROM invoices_copy
WHERE invoice_total - payment_total - credit_total = 0

Now, let’s see the target table again…

SELECT * 
FROM invoice_archive_copy
LIMIT 10

10 records

invoice_id	vendor_id	invoice_number	invoice_date	invoice_total	payment_total	credit_total	terms_id	invoice_due_date	payment_date
1	122	989319-457	2022-04-08	3813.33	3813.33	0	3	2022-05-08	NA
2	123	263253241	2022-04-10	40.20	40.20	0	3	2022-05-10	NA
3	123	963253234	2022-04-13	138.75	138.75	0	3	2022-05-13	NA
4	123	2-000-2993	2022-04-16	144.70	144.70	0	3	2022-05-16	NA
5	123	963253251	2022-04-16	15.50	15.50	0	3	2022-05-16	NA
6	123	963253261	2022-04-16	42.75	42.75	0	3	2022-05-16	NA
7	123	963253237	2022-04-21	172.50	172.50	0	3	2022-05-21	NA
8	89	125520-1	2022-04-24	95.00	95.00	0	1	2022-05-04	NA
9	121	97/488	2022-04-24	601.95	601.95	0	3	2022-05-24	NA
10	123	263253250	2022-04-24	42.67	42.67	0	3	2022-05-24	NA

UPDATE: Modify Records

Example: We want to manually update an invoice record with a particular invoice_number

Now, let’s see what it looks like before any updates

SELECT *
FROM invoices_copy
WHERE invoice_number = '97/522'

1 records

invoice_id	vendor_id	invoice_number	invoice_date	invoice_total	payment_total	credit_total	terms_id	invoice_due_date	payment_date
78	121	97/522	2022-06-28	1962.13	1762.13	200	3	2022-07-28	2022-07-30

Now, we will update the payment_date and payment_total values of the same invoice:

UPDATE invoices_copy
SET payment_date = '2022-09-21', 
    payment_total = 19351.18
WHERE invoice_number = '97/522'

Now, let’s see what it looks like after the updates:

SELECT *
FROM invoices_copy
WHERE invoice_number = '97/522'

1 records

invoice_id	vendor_id	invoice_number	invoice_date	invoice_total	payment_total	credit_total	terms_id	invoice_due_date	payment_date
78	121	97/522	2022-06-28	1962.13	19351.18	200	3	2022-07-28	2022-09-21

We can also update the new values with the calculation from the old values.

Example: We want to increase the credit total by 100

UPDATE invoices_copy
SET credit_total = credit_total + 100 
/* mathmatically, this does not make sense, but here, it simply means credit_total (new) = credit_total (old) + 100 */
WHERE invoice_number = '97/522'

Now, let’s see what it looks like after the second update:

SELECT *
FROM invoices_copy
WHERE invoice_number = '97/522'

1 records

invoice_id	vendor_id	invoice_number	invoice_date	invoice_total	payment_total	credit_total	terms_id	invoice_due_date	payment_date
78	121	97/522	2022-06-28	1962.13	19351.18	300	3	2022-07-28	2022-09-21

You can also use update a batch of records meeting criteria from a subquery.

Example: we want to set all terms_id to 1 (instead of current 2) for a vendor with name called ‘Pacific Bell’. This is only possible when querying with vendor table.

Now, let’s see what all invoices look with vendor Pacific Bell like, before any updates:

SELECT *
FROM invoices_copy
WHERE vendor_id IN
      (SELECT vendor_id
       FROM vendors
       WHERE vendor_name = 'Pacific Bell')

6 records

invoice_id	vendor_id	invoice_number	invoice_date	invoice_total	payment_total	credit_total	terms_id	invoice_due_date	payment_date
13	95	111-92R-10096	2022-04-30	16.33	16.33	0	2	2022-05-20	2022-05-23
44	95	111-92R-10094	2022-06-01	19.67	19.67	0	2	2022-06-21	2022-06-24
49	95	111-92R-10097	2022-06-04	16.33	16.33	0	2	2022-06-24	2022-06-26
53	95	111-92R-10092	2022-06-09	46.21	46.21	0	2	2022-06-29	2022-07-02
83	95	111-92R-10093	2022-07-06	39.77	39.77	0	2	2022-07-26	2022-07-22
91	95	111-92R-10095	2022-07-15	32.70	32.70	0	2	2022-08-04	2022-08-06

Now, we apply the updates:

UPDATE invoices_copy
SET terms_id = 1
WHERE vendor_id =
      (SELECT vendor_id
       FROM vendors
       WHERE vendor_name = 'Pacific Bell')

Now, let’s see what all invoices look with vendor Pacific Bell like, after updates:

SELECT *
FROM invoices_copy
WHERE vendor_id IN
      (SELECT vendor_id
       FROM vendors
       WHERE vendor_name = 'Pacific Bell')

6 records

invoice_id	vendor_id	invoice_number	invoice_date	invoice_total	payment_total	credit_total	terms_id	invoice_due_date	payment_date
13	95	111-92R-10096	2022-04-30	16.33	16.33	0	1	2022-05-20	2022-05-23
44	95	111-92R-10094	2022-06-01	19.67	19.67	0	1	2022-06-21	2022-06-24
49	95	111-92R-10097	2022-06-04	16.33	16.33	0	1	2022-06-24	2022-06-26
53	95	111-92R-10092	2022-06-09	46.21	46.21	0	1	2022-06-29	2022-07-02
83	95	111-92R-10093	2022-07-06	39.77	39.77	0	1	2022-07-26	2022-07-22
91	95	111-92R-10095	2022-07-15	32.70	32.70	0	1	2022-08-04	2022-08-06

NOTE: it is a good idea to query and test the criteria (WHERE clause), before you apply any updates. The same goes to next section “DELETE”, when removing records.

DELETE: Remove Records

Example: we want to remove all the last 3 inserted records: vendor_id: 116, 117, 118

DELETE FROM invoices_copy
WHERE invoice_id >= 116

Now, let’s see the result: (all records from 116 and above are gone)

SELECT *
FROM invoices_copy
WHERE invoice_id >= 115

1 records

invoice_id	vendor_id	invoice_number	invoice_date	invoice_total	payment_total	credit_total	terms_id	invoice_due_date	payment_date
115	97	456789	2022-08-01	8344.5	0	0	1	2022-08-31	NA